import pandas as pd
import numpy as np
from sqlalchemy import func, select, exc
from datetime import datetime
from app.model.RiskTitlelModel import RiskTitle
from app.config.db import DBUtils
from app.service.UserPortraitService import UserPortraitService

user_portrait = UserPortraitService()

class CapitalRiskService:
    def __init__(self):
        self.db_utils = DBUtils()

    # 查询资金风险
    def select_capital_risk(self, session, company, year, qylx):
        if year:
            # condition = RiskTitle.SCRQ.between(start_date, end_date)
            condition = RiskTitle.YEAR == year
        else:
            condition = RiskTitle.YEAR == datetime.now().year
        if qylx == 1:
            risk_gsmc = RiskTitle.GSMC == company
        elif qylx == 2:
            risk_gsmc = RiskTitle.GS == company
        elif qylx == 3:
            risk_gsmc = RiskTitle.GS.isnot(None)
        else:
            risk_gsmc = RiskTitle.GSMC == company
        try:
            query = select(
                func.sum(RiskTitle.FKJE).label("FKJE"),
                RiskTitle.FXLX,
                func.sum(RiskTitle.COUNT).label("COUNT"),
            ).where(
                risk_gsmc,
                condition
            ).group_by(RiskTitle.FXLX)
            risk_list = session.execute(query).all()

            total_type = 0
            total_count = 0
            total_amount = 0
            details_list = []
            for row in risk_list:
                detail_list = {
                    'TYPE': row.FXLX,
                    'COUNT': row.COUNT,
                    'FKJE': round(row.FKJE / 10000, 2) if row.FKJE >= 50 or row.FKJE == 0 else 0.01
                }
                details_list.append(detail_list)
                total_type += 1
                total_count += row.COUNT
                total_amount += row.FKJE
            risk_total ={
                'type': total_type,
                'count': total_count,
                'amount': round(total_amount/ 10000, 2) if total_amount >= 50 or total_amount == 0 else 0.01
            }
            # df = pd.DataFrame(risk_list, columns=['FKJE', 'FXLX', 'COUNT'])
            # count_total = df['COUNT'].sum()
            # amount_total = df['FKJE'].sum()
            # type_total = df['FXLX'].count()
            # risk_total = {
            #     "count": count_total,
            #     "amount": round(amount_total / 10000, 2) if amount_total >= 50 else 0.01,
            #     "type": type_total
            # }

            # details_total = (df.groupby('TYPE')
            #                  .agg({'FKJE': 'sum', 'COUNT': 'sum'})
            #                  .reset_index()
            #                  .assign(FKJE=lambda x: np.where(x['FKJE'] < 50, 0.01, round(x['FKJE'] / 10000, 2)))
            #                  )
            # details_list = details_total.to_dict(orient='records')
            result = {
                "risk_total": risk_total,
                "details_total": details_list
            }
            if result:
                return result
            else:
                result = []
                return result
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")



